import pandas as pd
from sqlalchemy import create_engine
import datetime
import openpyxl


def lambdaSellOrNot(sellsNumber):
    if int(sellsNumber) > 0:
        return 1
    else:
        return 0


def lambdaEmpty2Zero(sellsNumber):
    if pd.isnull(sellsNumber):
        return 0
    else:
        sellsNumber


# ERP信誉订单格式化店铺名称
def lambdaLinkShopNameWithERP(shopName):
    return shopName.split('-')[1]


# 商家后台格式化店铺名称
def lambdaLinkShopNameInSJHT(a):
    return a[:a.find('店') + 1]
    # return a


def generatePddOperationFull(engine, con):
    # ★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
    sql = " select statDate,shopName,cfmOrdrAmt,guv,cfmOrdrUsrCnt,cfmOrdrCnt from 拼多多_商家后台_流量数据_流量看板"
    df_P_HT_Llkb = pd.read_sql(sql=sql, con=engine)
    df_P_HT_Llkb.columns = ['拼多多_商家后台_流量数据_流量看板_' + str(col) for col in df_P_HT_Llkb.columns]

    # ★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
    sql = " select statDate,sucRfOrdrAmt1d,sucRfOrdrCnt1d from 拼多多_商家后台_服务数据_售后数据"
    df_shsj = pd.read_sql(sql=sql, con=engine)
    df_shsj.columns = ['拼多多_商家后台_服务数据_售后数据_' + str(col) for col in df_shsj.columns]
    dfAll = pd.merge(df_P_HT_Llkb, df_shsj, left_on=['拼多多_商家后台_流量数据_流量看板_statDate'],
                     right_on=['拼多多_商家后台_服务数据_售后数据_statDate'], how='left')

    # ★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
    sql = " select statDate,gpv,goodsFavCnt_Agg,vstGoodsCnt_Agg from 拼多多_商家后台_商品概况 where 运行模式='日模式' "
    df_spgk = pd.read_sql(sql=sql, con=engine)
    df_spgk.columns = ['拼多多_商家后台_商品概况_' + str(col) for col in df_spgk.columns]
    dfAll = pd.merge(dfAll, df_spgk, left_on=['拼多多_商家后台_流量数据_流量看板_statDate'],
                     right_on=['拼多多_商家后台_商品概况_statDate'], how='left')

    # ★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
    sql = " select statDate,payOrdrUsrCnt from 拼多多_商家后台_交易数据"
    df_jysj = pd.read_sql(sql=sql, con=engine)
    df_jysj.columns = ['拼多多_商家后台_交易数据_' + str(col) for col in df_jysj.columns]
    dfAll = pd.merge(dfAll, df_jysj, left_on=['拼多多_商家后台_流量数据_流量看板_statDate'],
                     right_on=['拼多多_商家后台_交易数据_statDate'], how='left')

    # ★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
    sql = " select statDate,客服销售额元,咨询人数,询单人数,最终成团人数,询单转化率 from 拼多多_商家后台_多多客服_客服数据"
    df_duoduokefu = pd.read_sql(sql=sql, con=engine)
    df_duoduokefu.columns = ['拼多多_商家后台_多多客服_客服数据_' + str(col) for col in df_duoduokefu.columns]
    dfAll = pd.merge(dfAll, df_duoduokefu, left_on=['拼多多_商家后台_流量数据_流量看板_statDate'],
                     right_on=['拼多多_商家后台_多多客服_客服数据_statDate'], how='left')

    # ★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
    sql = " select statDate from 拼多多_商家后台_服务数据_店铺领航员"
    df_dplhy = pd.read_sql(sql=sql, con=engine)
    df_dplhy.columns = ['拼多多_商家后台_服务数据_店铺领航员_' + str(col) for col in df_dplhy.columns]
    dfAll = pd.merge(dfAll, df_dplhy, left_on=['拼多多_商家后台_流量数据_流量看板_statDate'],
                     right_on=['拼多多_商家后台_服务数据_店铺领航员_statDate'], how='left')

    # ★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
    sql = " select statDate,descOver50RevScr3m from 拼多多_商家后台_服务数据_评价数据_店铺dsr"
    df_dp_dsr = pd.read_sql(sql=sql, con=engine)
    df_dp_dsr.columns = ['拼多多_商家后台_服务数据_评价数据_店铺dsr_' + str(col) for col in df_dp_dsr.columns]
    dfAll = pd.merge(dfAll, df_dp_dsr, left_on=['拼多多_商家后台_流量数据_流量看板_statDate'],
                     right_on=['拼多多_商家后台_服务数据_评价数据_店铺dsr_statDate'], how='left')

    # 拼多多_商家后台_流量数据_营销活动数据_商品数据

    # 访客支付转化率=成团买家数/商品访客数
    dfAll['访客支付转化率'] = dfAll['拼多多_商家后台_流量数据_流量看板_cfmOrdrUsrCnt'] / dfAll['拼多多_商家后台_流量数据_流量看板_guv']

    # 成团金额-客服销售额
    dfAll['静默销售额'] = dfAll['拼多多_商家后台_流量数据_流量看板_cfmOrdrAmt'] - dfAll['拼多多_商家后台_多多客服_客服数据_客服销售额元']
    # 静默销售额/(支付买家数-最终成团人数)
    dfAll['静默成团客单价'] = dfAll['静默销售额'] / (dfAll['拼多多_商家后台_交易数据_payOrdrUsrCnt'] - dfAll['拼多多_商家后台_多多客服_客服数据_最终成团人数'])
    dfAll['客服成团客单价'] = dfAll['拼多多_商家后台_多多客服_客服数据_客服销售额元'] / dfAll['拼多多_商家后台_多多客服_客服数据_最终成团人数']
    dfAll['成功退款金额占比'] = ''
    # (成团订单数-成功退款订单数)/成团订单数
    dfAll['成功退款订单数占比'] = (dfAll['拼多多_商家后台_流量数据_流量看板_cfmOrdrCnt'] - dfAll['拼多多_商家后台_服务数据_售后数据_sucRfOrdrCnt1d']) / dfAll[
        '拼多多_商家后台_流量数据_流量看板_cfmOrdrCnt']
    dfAll['成功退款订单数占比'] = dfAll['成功退款订单数占比'].apply(lambda x: format(x, '.2%'))

    # 和 拼多多_商家后台_商品列表_线上商品 进行关联
    sql = " select * from 拼多多_商家后台_商品列表_线上商品"
    df_online_goods = pd.read_sql(sql=sql, con=engine)
    df_online_goods['累计是否有销售'] = df_online_goods['sold_quantity'].apply(lambdaSellOrNot)
    df_online_goods['累计是否有销售30'] = df_online_goods['sold_quantity_for_thirty_days'].apply(lambdaSellOrNot)
    dfGroup = df_online_goods.groupby('statDate', as_index=False).agg({'id': "count", '累计是否有销售': "sum"})
    dfGroup.columns = ['商品列表_线上商品_groupby_' + str(col) for col in dfGroup.columns]
    dfAll = pd.merge(dfAll, dfGroup, left_on=['拼多多_商家后台_流量数据_流量看板_statDate'],
                     right_on=['商品列表_线上商品_groupby_statDate'], how='left')
    # 动销率=有销售的商品数/在线商品数
    # dfAll['动销率'] = dfAll['商品列表_线上商品_groupby_累计是否有销售'] / dfAll['商品列表_线上商品_groupby_id']
    dfAll['动销率'] = dfAll['商品列表_线上商品_groupby_累计是否有销售'] / dfAll['商品列表_线上商品_groupby_id']
    dfAll['动销率'] = dfAll['动销率'].apply(lambda x: format(x, '.2%'))
    # dfAll['商品列表_线上商品_groupby_id'] = dfAll['商品列表_线上商品_groupby_id'].astype("int")
    # 商品列表_线上商品_groupby_累计是否有销售
    dfAll['在线商品数'] = dfAll['商品列表_线上商品_groupby_id']
    # dfAll['有访问的商品数'] = ''   #实际名称为：被访问商品数
    dfAll['有销售的商品数'] = dfAll['商品列表_线上商品_groupby_累计是否有销售']
    # **********************************30日内
    df_online_goods30 = df_online_goods[
        df_online_goods['created_at'] > (datetime.datetime.now() + datetime.timedelta(days=-30)).strftime("%Y-%m-%d")]
    # 分组
    dfGroup30 = df_online_goods30.groupby('statDate', as_index=False).agg({'id': "count", '累计是否有销售30': "sum"})
    dfGroup30.columns = ['商品列表_线上商品_groupby30_' + str(col) for col in dfGroup30.columns]
    dfAll = pd.merge(dfAll, dfGroup30, left_on=['拼多多_商家后台_流量数据_流量看板_statDate'],
                     right_on=['商品列表_线上商品_groupby30_statDate'], how='left')
    dfAll['30日上新商品类别数'] = dfAll['商品列表_线上商品_groupby30_id']
    dfAll['30日上新商品有成交商品类别数'] = dfAll['商品列表_线上商品_groupby30_累计是否有销售30']
    dfAll['新品动销率'] = dfAll['30日上新商品有成交商品类别数'] / dfAll['30日上新商品类别数']
    dfAll['新品动销率'] = dfAll['新品动销率'].apply(lambda x: format(x, '.2%'))
    # ***********************************通过30日内的新品，再去通过商品id去关联，商品明细，在商品明细中，有浏览数据
    # dfAll['30日上新商品数总浏览'] = ''
    sql = " select * from 拼多多_商家后台_商品明细 where statDate='" + (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
        "%Y-%m-%d") + "' and 统计起始日='" + (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
        "%Y-%m-%d") + "' and 统计结束日='" + (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
        "%Y-%m-%d") + "' "
    df_goodsDetail = pd.read_sql(sql=sql, con=engine)
    df_goodsDetail.columns = ['商品明细浏览_' + str(col) for col in df_goodsDetail.columns]
    df_online_goods30_Browser = pd.merge(df_online_goods30, df_goodsDetail, left_on=['goods_id'],
                                         right_on=['商品明细浏览_goodsId'], how='left')
    # '商品明细浏览_cfmOrdrGoodsQty': 'sum'
    df_online_goods30_BrowserGroup = df_online_goods30_Browser.groupby('statDate', as_index=False).agg(
        {'商品明细浏览_goodsPv': 'sum'})
    df_online_goods30_BrowserGroup.rename(columns={'商品明细浏览_goodsPv ': '30日上新商品数总浏览'}, inplace=True)
    dfAll = pd.merge(dfAll, df_online_goods30_BrowserGroup, left_on=['拼多多_商家后台_流量数据_流量看板_statDate'],
                     right_on=['statDate'], how='left')
    # 需要进行位移
    dfAll = dfAll.drop_duplicates()
    dfAll.sort_values(['拼多多_商家后台_流量数据_流量看板_statDate'], ascending=True, inplace=True)
    # dfAll['商品收藏总数较前一天'] = dfAll['拼多多_商家后台_商品概况_goodsFavCnt_Agg'].fillna(0).shift(-1)
    dfAll['商品收藏数较昨天'] = dfAll['拼多多_商家后台_商品概况_goodsFavCnt_Agg'].fillna(0).astype("int").diff(periods=1)
    dfAll['商品收藏总数较前一天'] = dfAll['拼多多_商家后台_商品概况_goodsFavCnt_Agg'].fillna(0).astype("int").diff(periods=2)
    dfAll['前天商品收藏总数'] = dfAll['拼多多_商家后台_商品概况_goodsFavCnt_Agg'].fillna(0).astype("int") - dfAll['商品收藏总数较前一天'].fillna(
        0).astype("int")
    # dfAll['商品收藏总数较前一天'] = dfAll['拼多多_商家后台_商品概况_goodsFavCnt_Agg'].fillna(0).astype("int") - 500
    # 某列的空值，临时填充为0
    dfAll['昨天商品收藏总数'] = dfAll['拼多多_商家后台_商品概况_goodsFavCnt_Agg'].fillna(0).astype("int") - dfAll['商品收藏数较昨天'].fillna(
        0).astype("int")

    # 商品成团件数
    sql = " select * from 拼多多_商家后台_商品明细 "
    df_goodsDetailAll = pd.read_sql(sql=sql, con=engine)
    df_goodsDetailAll.columns = ['商品明细浏览_' + str(col) for col in df_goodsDetailAll.columns]

    df_goodsDetailAll['商品明细浏览_cfmOrdrGoodsQty'] = df_goodsDetailAll['商品明细浏览_cfmOrdrGoodsQty'].astype("int")
    df_goodsDetailCfmGroup = df_goodsDetailAll.groupby('商品明细浏览_statDate', as_index=False).agg(
        {'商品明细浏览_cfmOrdrGoodsQty': 'sum'})
    dfAll = pd.merge(dfAll, df_goodsDetailCfmGroup, left_on=['拼多多_商家后台_流量数据_流量看板_statDate'],
                     right_on=['商品明细浏览_statDate'], how='left')
    dfAll['每天收藏到成交的转化率'] = dfAll['商品明细浏览_cfmOrdrGoodsQty'] / dfAll['昨天商品收藏总数']
    dfAll['每天收藏到成交的转化率'] = dfAll['每天收藏到成交的转化率'].apply(lambda x: format(x, '.2%'))

    # 这里需要的是实际数据
    sql = " select 店铺,平台,统计时间,订单付款金额,任务佣金,结算金额,任务单号 from 云杉erp_信誉订单总表 "
    dfERPZhongCai = pd.read_sql(sql=sql, con=engine)
    dfERPZhongCai['店铺ERP_关联'] = dfERPZhongCai['店铺'].apply(lambdaLinkShopNameWithERP)
    dfERPZhongCaiGroup = dfERPZhongCai.groupby(['店铺ERP_关联', '统计时间'], as_index=False).agg(
        {"订单付款金额": "sum", "任务佣金": "sum", "结算金额": "sum", "任务单号": "count"})
    dfERPZhongCaiGroup['统计时间'] = dfERPZhongCaiGroup['统计时间'].apply(lambda x: str(x))
    # dfERPZhongCaiGroup['统计时间'] = pd.to_date(dfERPZhongCaiGroup['统计时间'])
    dfAll['店铺名称商家后台'] = dfAll['拼多多_商家后台_流量数据_流量看板_shopName'].apply(lambdaLinkShopNameInSJHT)
    dfAll['statDateLinkERP'] = dfAll['拼多多_商家后台_流量数据_流量看板_statDate'].apply(lambda x: str(x))
    # dfAll = pd.merge(dfAll, dfERPZhongCaiGroup, left_on=['拼多多_商家后台_流量数据_流量看板_statDate', '店铺名称商家后台'],
    #                  right_on=['统计时间', '店铺ERP_关联'], how='left')
    dfAll = pd.merge(dfAll, dfERPZhongCaiGroup, left_on=['店铺名称商家后台', 'statDateLinkERP'],
                     right_on=['店铺ERP_关联', '统计时间'], how='left')

    dfAll['刷单销售额'] = dfAll['订单付款金额']
    dfAll['真实销售额'] = dfAll['拼多多_商家后台_流量数据_流量看板_cfmOrdrAmt'] - dfAll['任务佣金']
    dfAll['真实买家数'] = dfAll['拼多多_商家后台_流量数据_流量看板_cfmOrdrUsrCnt'] - dfAll['任务单号']
    dfAll['刷单订单数'] = dfAll['任务单号']
    dfAll['真实订单数'] = dfAll['拼多多_商家后台_流量数据_流量看板_cfmOrdrCnt'] - dfAll['任务单号']

    # 最后一批补的
    dfAll['访客价值'] = dfAll['真实销售额'] / dfAll['拼多多_商家后台_流量数据_流量看板_guv']
    dfAll['成团客单价'] = dfAll['真实销售额'] / (dfAll['拼多多_商家后台_流量数据_流量看板_guv'] * dfAll['访客支付转化率'])
    dfAll['成团订单均价'] = dfAll['真实销售额'] / dfAll['拼多多_商家后台_流量数据_流量看板_cfmOrdrCnt']

    dfAll['刷单买家数'] = dfAll['任务单号']

    # 多多搜索的花费
    sql = " select spend,statDate,click,shopName,运行模式 from 拼多多_商家后台_多多客服_推广计划_多多搜索 where 运行模式='日模式' "
    dfSearchCost = pd.read_sql(sql=sql, con=engine)
    dfSearchCost.columns = ['拼多多_商家后台_多多客服_推广计划_多多搜索_' + str(col) for col in dfSearchCost.columns]
    dfAll = pd.merge(dfAll, dfSearchCost, left_on=['拼多多_商家后台_流量数据_流量看板_statDate'],
                     right_on=['拼多多_商家后台_多多客服_推广计划_多多搜索_statDate'], how='left')


    # 这里每次都重新删除掉，因此用的是replace
    dfAll = dfAll.drop_duplicates()  # 删除所有数据都重复的行
    # 解决可能出现的超时问题bugfix 2020.08.18
    con.connection.connection.ping(reconnect=True)
    engine.execute("delete from 拼多多_店铺运营总表")
    dfAll.to_sql(name='拼多多_店铺运营总表', con=con, if_exists='append', index=False)
    debug = ''

    return


if __name__ == '__main__':
    # 拼凑拼多多运营总表的总数集合
    engine = create_engine('mysql+pymysql://jsbi:jsbi-1701@47.114.55.19:9011/biv1?charset=utf8')
    con = engine.connect()
    generatePddOperationFull(engine, con)
